﻿using EcoSystem.Office.Attributes;
using EcoSystem.Office.Enums;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.Serialization;
using System.Text;

namespace EcoSystem.Office.Excel
{
    /// <summary>
    /// 
    /// </summary>
    public static class ExcelImportService
    {

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="filePath"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static List<TModel> ImportData<TModel>(string filePath, ExcelImportOptions options) where TModel : class, new()
        {
            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                return ImportData<TModel>(fs, options);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="stream"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static List<TModel> ImportData<TModel>(Stream stream, ExcelImportOptions options) where TModel : class, new()
        {
            IWorkbook workbook;
            if (options.FileType == ExcelFileType.Excel2007)
            {
                workbook = new XSSFWorkbook(stream);
            }
            else
            {
                workbook = new HSSFWorkbook(stream);
            }
            ISheet sheet = workbook.GetSheetAt(options.SheetIndex);
            DataTable table = GetSheetData<TModel>(sheet, options);
            return ConvertToList<TModel>(table, options.Columns);
        }

        /// <summary>
        /// 读Excel单元格的数据
        /// </summary>
        /// <param name="cell">Excel单元格</param>
        /// <param name="type">列数据类型</param>
        /// <returns>object 单元格数据</returns>
        private static object GetCellData(ICell cell, Type type)
        {
            if (cell == null)
            {
                return null;
            }
            if (type.IsEnum)
            {
                //枚举处理
                return GetEnumValue(type, cell);
            }
            switch (cell.CellType)
            {
                case CellType.Numeric:
                    return GetNumberValue(type, cell);
                case CellType.String:
                    if (type == typeof(DateTime))
                    {
                        //日期处理
                        return cell.DateCellValue;
                    }
                    return cell.StringCellValue;
                case CellType.Boolean:
                    return cell.BooleanCellValue;

                case CellType.Unknown:
                case CellType.Formula:
                case CellType.Blank:
                case CellType.Error:
                    return null;
                default:
                    return null;
            }
        }

        /// <summary>
        /// 处理数值类型字段
        /// </summary>
        /// <param name="type"></param>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetNumberValue(Type type, ICell cell)
        {
            if (type == typeof(DateTime))
            {
                return cell.DateCellValue;
            }
            else if (type == typeof(byte))
            {
                return (byte)cell.NumericCellValue;
            }
            if (type == typeof(short))
            {
                return (short)cell.NumericCellValue;
            }
            else if (type == typeof(int))
            {
                return (int)cell.NumericCellValue;
            }
            else if (type == typeof(long))
            {
                return (long)cell.NumericCellValue;
            }
            if (type == typeof(ushort))
            {
                return (ushort)cell.NumericCellValue;
            }
            else if (type == typeof(uint))
            {
                return (uint)cell.NumericCellValue;
            }
            else if (type == typeof(ulong))
            {
                return (ulong)cell.NumericCellValue;
            }
            else if (type == typeof(decimal))
            {
                return (decimal)cell.NumericCellValue;
            }
            else if (type == typeof(float))
            {
                return (float)cell.NumericCellValue;
            }
            return (int)cell.NumericCellValue;
        }

        /// <summary>
        /// 枚举类型字段取值
        /// </summary>
        /// <param name="type"></param>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetEnumValue(Type type, ICell cell)
        {
            if (cell.CellType == CellType.Numeric)
            {
                //数字类型转换
                return Convert.ChangeType((int)cell.NumericCellValue, type);
            }
            else
            {
                //字符串转换
                string value = cell.StringCellValue;
                var list = EnumHelper.GetEnums(type);
                var item = list.FirstOrDefault(s =>
                s.Name.Equals(value, StringComparison.OrdinalIgnoreCase) ||
                s.Descrption.Equals(value, StringComparison.OrdinalIgnoreCase));
                if (item == null)
                {
                    return type.IsValueType ? Activator.CreateInstance(type) : null;
                }
                try
                {
                    return Enum.Parse(type, item.Name, true);
                }
                catch
                {
                    return type.IsValueType ? Activator.CreateInstance(type) : null;
                }
            }
        }


        /// <summary>
        /// 读取Sheet中的数据到DataTable
        /// </summary>
        /// <param name="sheet">当前数据表</param>
        /// <returns>DataTable</returns>
        private static DataTable GetSheetData<T>(ISheet sheet, ExcelImportOptions options)
        {
            var table = InitTable<T>(sheet, options);
            if (table == null) return null;

            var rows = sheet.GetEnumerator();
            while (rows.MoveNext())
            {
                var row = (IRow)rows.Current;
                if (row.RowNum == 0) continue;

                var dr = table.NewRow();
                for (var i = 0; i < table.Columns.Count; i++)
                {
                    try
                    {
                        var type = table.Columns[i].DataType;
                        dr[i] = GetCellData(row.GetCell(i), type);
                    }
                    catch (Exception)
                    {
                        dr[i] = DBNull.Value;
                    }

                }
                table.Rows.Add(dr);
            }

            if (options.IsTemplate)
            {
                //如果导出模块文件填写数据再导入需要处理数据约束字段信息
                var book = sheet.Workbook;

                var sheetIndex = book.GetSheetIndex("DATA");
                if (sheetIndex >= 0)
                {
                    var dataSheet = book.GetSheetAt(sheetIndex);
                    if (dataSheet != null)
                    {
                        var dataRow = dataSheet.GetRow(1);
                        if (dataRow != null)
                        {
                            var dataCell = dataRow.GetCell(0);
                            if (dataCell != null)
                            {
                                ProcessValidationData(table, dataCell.StringCellValue);
                            }
                        }
                    }
                }
            }
            return table;
        }

        /// <summary>
        /// 数据数据验证匹配
        /// </summary>
        /// <param name="table"></param>
        /// <param name="strValidateOptions"></param>
        private static void ProcessValidationData(DataTable table, string strValidateOptions)
        {
            var json = string.IsNullOrWhiteSpace(strValidateOptions) ? "[]" : strValidateOptions;
            var validates = JsonConvert.DeserializeObject<List<ExcelCellValidateData>>(json);
            foreach (var item in validates)
            {
                string columnName = null;
                if (table.Columns.Contains(item.Caption))
                {
                    columnName = item.Caption;
                }
                else if (table.Columns.Contains(item.FieldName))
                {
                    columnName = item.FieldName;
                }

                foreach (DataRow row in table.Rows)
                {
                    string text = Convert.ToString(row[columnName]);
                    var opt = item.Options.FirstOrDefault(s => s.CellText == text);
                    if (opt != null)
                    {
                        row[columnName] = opt.CellValue;
                    }
                }
            }
            if (validates.Count > 0)
            {
                table.AcceptChanges();
            }
        }

        /// <summary>
        /// 获取指定类型的属性名称/类型字典
        /// </summary>
        /// <returns>Dictionary</returns>
        private static Dictionary<string, Type> GetDictionary<T>(ISheet sheet, ExcelImportOptions options)
        {
            var dict = new Dictionary<string, Type>();
            if (typeof(T) == typeof(Dictionary<string, object>))
            {

                if (options.Columns.Count > 0)
                {
                    foreach (var col in options.Columns)
                    {
                        string name = string.IsNullOrWhiteSpace(col.AliasName) ? col.FieldName : col.AliasName;
                        dict.Add(name, col.DataType);
                    }
                }
                else
                {
                    int colIndex = 0;
                    IRow row = sheet.GetRow(0);
                    IRow row2 = sheet.GetRow(1);
                    while (true)
                    {
                        var cell = row.GetCell(colIndex);
                        if (cell == null)
                        {
                            break;
                        }
                        if (row2 == null)
                        {
                            dict.Add(cell.StringCellValue, typeof(string));
                        }
                        else
                        {
                            var cell2 = row2.GetCell(colIndex);
                            dict.Add(cell.StringCellValue, GetCellType(cell2));
                        }
                        colIndex++;
                    }
                }
            }
            else
            {
                var propertys = typeof(T).GetProperties();
                foreach (var p in propertys)
                {
                    string name;
                    var attributes = p.GetCustomAttributes(typeof(AliasAttribute), false);
                    if (attributes.Length > 0)
                    {
                        var type = (AliasAttribute)attributes[0];
                        name = type.Alias;
                    }
                    else
                    {
                        name = p.Name;
                    }
                    dict.Add(name, p.PropertyType);
                }
            }
            return dict;
        }

        private static Type GetCellType(ICell cell)
        {
            if (cell == null)
            {
                return typeof(string);
            }
            else if (cell.CellType == CellType.Numeric)
            {
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    return typeof(DateTime);
                }
                return typeof(decimal);
            }
            else if (cell.CellType == CellType.Boolean)
            {
                return typeof(bool);
            }
            else
            {
                return typeof(string);
            }
        }

        /// <summary>
        /// 初始化DataTable
        /// </summary>
        /// <param name="sheet">当前数据表</param>
        /// <returns>DataTable</returns>
        private static DataTable InitTable<T>(ISheet sheet, ExcelImportOptions options)
        {
            var title = sheet.GetRow(0);
            if (title == null)
            {
                return null;
            }
            try
            {
                var dict = GetDictionary<T>(sheet, options);
                var table = new DataTable();
                foreach (var cell in title.Cells)
                {
                    var col_name = cell.StringCellValue;
                    var col_type = dict[col_name] ?? typeof(string);
                    table.Columns.Add(cell.StringCellValue, col_type);
                }
                return table;
            }
            catch (Exception ex)
            {
                throw ex;
                return null;
            }
        }

        /// <summary>
        /// 将DataTable转为List
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <returns>List</returns>
        public static List<T> ConvertToList<T>(DataTable table, List<ImportColumn> columns = null) where T : new()
        {
            if (table == null) { return null; }
            var list = new List<T>();

            if (typeof(Dictionary<string, object>) == typeof(T))
            {
                //字典类型数据处理
                foreach (DataRow row in table.Rows)
                {
                    var dict = new Dictionary<string, object>();
                    foreach (DataColumn col in table.Columns)
                    {
                        ImportColumn importColumn = columns == null ? null : columns.FirstOrDefault(s => s.AliasName == col.ColumnName);
                        var value = row[col.ColumnName];
                        string fieldName = importColumn == null ? col.ColumnName : importColumn.FieldName;
                        dict[fieldName] = value;
                    }
                    list.Add((T)(object)dict);
                }
            }
            else
            {
                //其它Model类型数据处理
                var propertys = typeof(T).GetProperties();
                foreach (DataRow row in table.Rows)
                {
                    var obj = new T();
                    foreach (var p in propertys)
                    {
                        string name;
                        var attributes = p.GetCustomAttributes(typeof(AliasAttribute), false);
                        if (attributes.Length > 0)
                        {
                            var type = (AliasAttribute)attributes[0];
                            name = type.Alias;
                        }
                        else
                        {
                            name = p.Name;
                        }

                        if (table.Columns.Contains(name))
                        {
                            if (!p.CanWrite) continue;

                            var value = row[name];
                            if (value == DBNull.Value) value = null;

                            p.SetValue(obj, value, null);
                        }
                    }
                    list.Add(obj);
                }
            }
            return list;
        }
    }

}
